接著講解Dapper Multi Mapping
(多對應)實作跟底層邏輯,畢竟工作當中不可能都是一對一概念。
使用方式 :
Query<Func邏輯>(SQL,Parameter,Mapping邏輯Func)
Query<Func第一個類別,Func第二個類別,..以此類推,Func最後返回類別>
(最多支持六組泛型參數)ID
,假如不一樣需要特別指定 (這段後面特別講解)由左至右
舉例 : 有訂單(Order)跟會員(User)表格,關係是一對多關係,一個會員可以有多個訂單,以下是C# Demo代碼 :
void Main()
{
using (var ts = new TransactionScope())
using (var cn = new SqlConnection(@"Data Source=(localdb)\MSSQLLocalDB;Integrated Security=SSPI;Initial Catalog=master;"))
{
cn.Execute(@"
CREATE TABLE [User]([ID] int, [Name] nvarchar(10));
INSERT INTO [User]([ID], [Name])VALUES(1, N'大雄'),(2, N'小明');
CREATE TABLE [Order]([ID] int, [OrderNo] varchar(13), [UserID] int);
INSERT INTO [Order]([ID], [OrderNo], [UserID])VALUES(1, 'SO20190900001', 1),(2, 'SO20190900002', 1),(3, 'SO20190900003', 2),(4, 'SO20190900004', 2);
");
var result = cn.Query<Order,User,Order>(@"
select * from [order] T1
left join [User] T2 on T1.UserId = T2.ID
", (order, user) => {
order.User = user;
return order;
}
);
ts.Dispose();
}
}
public class Order
{
public int ID { get; set; }
public string OrderNo { get; set; }
public User User { get; set; }
}
public class User
{
public int ID { get; set; }
public string Name { get; set; }
}
在初期常變動表格結構或是一次性功能不想宣告Class,Dapper Multi Mapping也支援dynamic方式
void Main()
{
using (var ts = new TransactionScope())
using (var connection = Connection)
{
const string createSql = @"
create table Users (Id int, Name nvarchar(20))
create table Posts (Id int, OwnerId int, Content nvarchar(20))
insert Users values(1, N'小明')
insert Users values(2, N'小智')
insert Posts values(101, 1, N'小明第1天日記')
insert Posts values(102, 1, N'小明第2天日記')
insert Posts values(103, 2, N'小智第1天日記')
";
connection.Execute(createSql);
const string sql =
@"select * from Posts p
left join Users u on u.Id = p.OwnerId
Order by p.Id
";
var data = connection.Query<dynamic, dynamic, dynamic>(sql, (post, user) => { post.Owner = user; return post; }).ToList();
}
}
Split預設是用來切割主鍵,所以預設切割字串是Id
,假如當表格結構PK名稱為Id
可以省略參數,舉例
var result = cn.Query<Order,User,Order>(@"
select * from [order] T1
left join [User] T2 on T1.UserId = T2.ID
", (order, user) => {
order.User = user;
return order;
}
);
假如主鍵名稱是其他名稱,請指定splitOn字串名稱
,並且對應多個可以使用,
做區隔,舉例,添加商品表格做Join :
var result = cn.Query<Order,User,Item,Order>(@"
select * from [order] T1
left join [User] T2 on T1.UserId = T2.ID
left join [Item] T3 on T1.ItemId = T3.ID
"
,map : (order, user,item) => {
order.User = user;
order.Item = item;
return order;
}
,splitOn : "Id,Id"
);